Additional material

Interrupting the tuning session

To stop the tuning sessions the user has two equivalent options:

1. From the CLI: Press Ctrl-c to abort the tuning session. You will be prompted to choose between reinstating the initial configuration and installing the best configuration found so far.
2. From the dashboard screen: Press the Stop tuning button in the web interface. This will install the best configuration found so far on the system.

Configuration management panel

The configuration management panel can be found below the performance monitoring plot on the DBtune dashboard screen. This allows you to perform specific actions regarding the database configurations explored by DBtune.

1. "Restore initial" button:

   Stops the tuning session and reinstates the default system configuration. Note that once the tuning session is aborted or completed, this action can not be performed.

2. "Find best" button:

   It allows the user to trace the best performing configuration found by DBtune. After pushing the button the user will identify with a circle the best performing configuration in the performance plot as shown below.

3. "Download all" button:

   Allows the user to download the history of configurations applied on the user’s database along with the achieved database throughput and/or query runtime.

4. "Apply this" button:

   Allows the user to install a specific configuration from the tuning history on the user database instance. Note that you need to select a configuration from the tuning summary plot before using this feature. Once the tuning session is aborted or completed, this action can not be performed.

5. "Download this" button:

   Allows you to download the PostgreSQL configuration file named “99_dbtune.conf” for the selected configuration.

How to run subsequent tuning sessions

Once the initial tuning session is completed, the “Stop tuning” button is replaced by a “Start tuning” button. Before starting a new tuning session, you need to run the DBtune agent. When you press the "Start tuning" button, a popup window will give you the command to run on your CLI. Run the command on your cloud instance as sudo user and use a terminal multiplexer (see step 3 above), to start a new tuning session from the beginning. Previous tuning sessions will appear under the “History” tab on the left.

Modify memory usage guardrail threshold

As a default behavior DBtune will automatically react and move away from PostgreSQL configurations that make the database instance use more than 90% available memory. This threshold can be customized in the DBtune YAML configuration files to accommodate specific user requirements. This can be achieved by adding the following line to the dbtune_config.yml file located in the downloaded dbtune_client:

tuning_session:
  maximum_memory_usage_allowed: 90

Mitigation of temporary low performance during the tuning session

DBtune also implements a guardrail to monitor the performance of each database configuration that is used on the customer instance during the tuning session. If the performance of a configuration is below 40% of the best found configuration so far, then DBtune automatically abandons that configuration by moving to the next iteration and recommending a new alternative configuration. This threshold can also be tweaked to meet specific user requirements. This can be achieved by adding the following line to the dbtune_config.yml file located in the downloaded dbtune_client:

tuning_session:
  early_measurement_threshold: 40

Choose alternative optimization target

The default target optimization is average query runtime which is a form of latency (measured in milliseconds). Tuning for average query runtime will also have the effect of improving the number of transactions per second (TPS) which is a form of throughput.

It is possible to put more emphasis on explicitly tune for throughput in the Additional tuning options in the Start tuning database page.

The target options are either:

1. Throughput: measured in transactions per second (TPS), or
2. Average query runtime: this represents latency, which is measured in milliseconds (ms).

The recommended target is average query runtime, which we have seen works in most cases for both metrics. In case you want to be more intentional about the objective that you chose, we recommend choosing:

1. Throughput if your machine/workload is hitting a bottleneck (either close to 100% CPU or 100% I/O), or in other words, an increased workload wouldn’t lead to increased throughput. In this scenario DBtune can help you to increase the overall throughput of your workload and achieve improved performance at peak.
2. Average query runtime if your workload is composed of complex queries and you wish to reduce the average runtime of one query. The average query runtime can improve even when the machine is not bottlenecked quite as intensely. Bigger database instances will drive bigger query runtime improvement.

Note that typically, improving one optimization target will also benefit the other target. However, this choice allows the optimizer to focus on an optimization objective.